USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetOpportunityTypeXml')
	DROP FUNCTION dbo.GetOpportunityTypeXml
GO


CREATE FUNCTION [dbo].[GetOpportunityTypeXml] ()
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@xml			varchar(max)

	SET @xml	= '<OPPORTUNITYTYPES><entry id=""></entry>'

	select @xml = @xml + '<entry id="' + CAST([OPPTYPE] AS VARCHAR)+ '"><![CDATA['+TypeDesc +']]></entry>' 
	from TR_OPPTYPE order by [OPPTYPE]
	set @xml = @xml + '</OPPORTUNITYTYPES>'

	return @xml
END


-- select dbo.GETOPPORTUNITYTYPEXML()